const sqlite3 = require('sqlite3').verbose();
const bcrypt = require('bcrypt');
const { faker } = require('@faker-js/faker');

// Create a new database or open existing one
const db = new sqlite3.Database('./database.sqlite', (err) => {
  if (err) {
    console.error('Error opening database:', err.message);
    return;
  }
  console.log('Connected to the SQLite database.');
  
  // Enable foreign keys
  db.run('PRAGMA foreign_keys = ON');
  
  // Start generating and inserting data
  generateData();
});

async function generateData() {
  try {
    // Generate data in sequence to maintain referential integrity
    await generateUsers();
    await generateCourses();
    await generateEnrollments();
    
    console.log('Mock data generation completed successfully!');
    
    // Close the database connection
    db.close((err) => {
      if (err) {
        console.error('Error closing database:', err.message);
      } else {
        console.log('Database connection closed.');
      }
    });
  } catch (error) {
    console.error('Error generating mock data:', error);
    db.close();
  }
}

// Generate users (teachers and students)
function generateUsers() {
  return new Promise(async (resolve, reject) => {
    try {
      // Generate 50 teachers
      const teacherPromises = [];
      for (let i = 0; i < 50; i++) {
        const teacher = {
          username: `teacher${i + 100}`,
          password: await bcrypt.hash('teacher123', 10),
          name: faker.person.fullName(),
          role: 'teacher',
          email: faker.internet.email(),
          phone: faker.helpers.fromRegExp('###-###-####')
        };
        teacherPromises.push(insertUser(teacher));
      }
      
      // Generate 950 students
      const studentPromises = [];
      for (let i = 0; i < 950; i++) {
        const student = {
          username: `student${i + 100}`,
          password: await bcrypt.hash('student123', 10),
          name: faker.person.fullName(),
          role: 'student',
          email: faker.internet.email(),
          phone: faker.helpers.fromRegExp('###-###-####')
        };
        studentPromises.push(insertUser(student));
      }
      
      // Wait for all inserts to complete
      await Promise.all([...teacherPromises, ...studentPromises]);
      console.log('Generated 1000 users (50 teachers, 950 students)');
      resolve();
    } catch (error) {
      reject(error);
    }
  });
}

// Insert a user into the database
function insertUser(user) {
  return new Promise((resolve, reject) => {
    const { username, password, name, role, email, phone } = user;
    db.run(
      `INSERT INTO users (username, password, name, role, email, phone)
       VALUES (?, ?, ?, ?, ?, ?)`,
      [username, password, name, role, email, phone],
      function(err) {
        if (err) {
          // If username already exists, just resolve (don't treat as error)
          if (err.message.includes('UNIQUE constraint failed')) {
            resolve();
          } else {
            reject(err);
          }
        } else {
          resolve(this.lastID);
        }
      }
    );
  });
}

// Generate courses
function generateCourses() {
  return new Promise((resolve, reject) => {
    // Get all teacher IDs
    db.all(`SELECT id FROM users WHERE role = 'teacher'`, async (err, teachers) => {
      if (err) {
        reject(err);
        return;
      }
      
      try {
        // Generate 200 courses
        const coursePromises = [];
        const departments = ['CS', 'MATH', 'PHYS', 'CHEM', 'BIO', 'ENG', 'HIST', 'ECON', 'PSYCH', 'ART'];
        const locations = ['Building A', 'Building B', 'Building C', 'Science Center', 'Arts Center', 'Library'];
        
        for (let i = 0; i < 200; i++) {
          // Randomly select a department and course number
          const dept = departments[Math.floor(Math.random() * departments.length)];
          const courseNum = Math.floor(Math.random() * 500) + 100;
          const code = `${dept}${courseNum}`;
          
          // Randomly select a teacher
          const teacherId = teachers[Math.floor(Math.random() * teachers.length)].id;
          
          // Generate random schedule
          const days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday'];
          const day = days[Math.floor(Math.random() * days.length)];
          const startHour = Math.floor(Math.random() * 8) + 8; // 8 AM to 4 PM
          const endHour = startHour + Math.floor(Math.random() * 3) + 1; // 1-3 hours long
          const schedule = `${day} ${startHour}:00-${endHour}:00`;
          
          // Generate random location
          const building = locations[Math.floor(Math.random() * locations.length)];
          const roomNum = Math.floor(Math.random() * 500) + 100;
          const location = `${building} Room ${roomNum}`;
          
          const course = {
            code,
            name: faker.lorem.words({ min: 2, max: 5 }).split(' ').map(word => 
              word.charAt(0).toUpperCase() + word.slice(1)
            ).join(' '),
            description: faker.lorem.paragraph(),
            credit: [1.0, 2.0, 3.0, 4.0, 5.0][Math.floor(Math.random() * 5)],
            capacity: Math.floor(Math.random() * 50) + 20, // 20-70 capacity
            teacher_id: teacherId,
            location,
            schedule
          };
          
          coursePromises.push(insertCourse(course));
        }
        
        // Wait for all course inserts to complete
        await Promise.all(coursePromises);
        console.log('Generated 200 courses');
        resolve();
      } catch (error) {
        reject(error);
      }
    });
  });
}

// Insert a course into the database
function insertCourse(course) {
  return new Promise((resolve, reject) => {
    const { code, name, description, credit, capacity, teacher_id, location, schedule } = course;
    db.run(
      `INSERT INTO courses (code, name, description, credit, capacity, current_enrollment, teacher_id, location, schedule)
       VALUES (?, ?, ?, ?, ?, 0, ?, ?, ?)`,
      [code, name, description, credit, capacity, teacher_id, location, schedule],
      function(err) {
        if (err) {
          // If course code already exists, just resolve (don't treat as error)
          if (err.message.includes('UNIQUE constraint failed')) {
            resolve();
          } else {
            reject(err);
          }
        } else {
          resolve(this.lastID);
        }
      }
    );
  });
}

// Generate enrollments
function generateEnrollments() {
  return new Promise((resolve, reject) => {
    // Get all student IDs
    db.all(`SELECT id FROM users WHERE role = 'student'`, (err, students) => {
      if (err) {
        reject(err);
        return;
      }
      
      // Get all course IDs
      db.all(`SELECT id, capacity FROM courses`, async (err, courses) => {
        if (err) {
          reject(err);
          return;
        }
        
        try {
          // Generate approximately 3000 enrollments (about 3 courses per student on average)
          const enrollmentPromises = [];
          const enrollmentSet = new Set(); // To prevent duplicate enrollments
          
          // For each student, enroll in 1-5 random courses
          for (const student of students) {
            const numCourses = Math.floor(Math.random() * 5) + 1; // 1-5 courses per student
            const shuffledCourses = [...courses].sort(() => 0.5 - Math.random());
            
            for (let i = 0; i < Math.min(numCourses, shuffledCourses.length); i++) {
              const course = shuffledCourses[i];
              const enrollmentKey = `${student.id}-${course.id}`;
              
              // Skip if this student is already enrolled in this course
              if (enrollmentSet.has(enrollmentKey)) {
                continue;
              }
              
              enrollmentSet.add(enrollmentKey);
              
              // Randomly assign a status (90% selected, 10% dropped)
              const status = Math.random() < 0.9 ? 'selected' : 'dropped';
              
              // Only assign scores to some of the selected courses (70% chance)
              let score = null;
              if (status === 'selected' && Math.random() < 0.7) {
                // Generate a score between 0 and 100
                score = Math.floor(Math.random() * 101);
              }
              
              const enrollment = {
                student_id: student.id,
                course_id: course.id,
                status,
                score
              };
              
              enrollmentPromises.push(insertEnrollment(enrollment));
            }
          }
          
          // Wait for all enrollment inserts to complete
          await Promise.all(enrollmentPromises);
          console.log(`Generated ${enrollmentPromises.length} enrollments`);
          
          // Update current_enrollment counts for all courses
          await updateEnrollmentCounts();
          
          resolve();
        } catch (error) {
          reject(error);
        }
      });
    });
  });
}

// Insert an enrollment into the database
function insertEnrollment(enrollment) {
  return new Promise((resolve, reject) => {
    const { student_id, course_id, status, score } = enrollment;
    db.run(
      `INSERT INTO enrollments (student_id, course_id, status, score)
       VALUES (?, ?, ?, ?)`,
      [student_id, course_id, status, score],
      function(err) {
        if (err) {
          // If enrollment already exists, just resolve (don't treat as error)
          if (err.message.includes('UNIQUE constraint failed')) {
            resolve();
          } else {
            reject(err);
          }
        } else {
          resolve(this.lastID);
        }
      }
    );
  });
}

// Update current_enrollment counts for all courses
function updateEnrollmentCounts() {
  return new Promise((resolve, reject) => {
    db.run(`
      UPDATE courses
      SET current_enrollment = (
        SELECT COUNT(*)
        FROM enrollments
        WHERE enrollments.course_id = courses.id
        AND enrollments.status = 'selected'
      )
    `, function(err) {
      if (err) {
        reject(err);
      } else {
        console.log('Updated current_enrollment counts for all courses');
        resolve();
      }
    });
  });
}
